﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace auxiliary_proj
{
    public partial class UCPurchaseOrder : UserControl
    {
        bool isError = false;

        DataTable result = new DataTable();

        public UCPurchaseOrder()
        {
            InitializeComponent();
            lblPhone.Text = "";
            lblName.Text = "";
            lblAddress.Text = "";

            resetFields();

            lbEmployee.Text = Employee.Name;
            qGetVendorName();

            PODataGridView.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige;
        }

        bool isValidPO = false;

        private void calculatorToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("calc");
        }

        private void POSubmitBtn_Click(object sender, EventArgs e)
        {
            isError = false;
            foreach (DataGridViewRow row in PODataGridView.Rows)
                row.ErrorText = "";

            List<String> partialQuery = new List<String>();

            if (isValidPO)
            {
                //holds [vendorCode],[quantity],[price] to later build INSERT when obtaining PO #

                //run through each row, ignore rows with [empty, NaN, or 0 quantity/price]
                foreach (DataGridViewRow row in PODataGridView.Rows)
                {
                    //if quantity or price is null (cell content is null when empty), skip
                    if (row.Cells["Quantity"].Value == null || row.Cells["Price"].Value == null)
                    {
                        row.ErrorText = "Invalid Row Data!";
                        isError = true;
                    }

                    int quantity;
                    bool tryQuantity = int.TryParse(row.Cells["Quantity"].Value.ToString(), out quantity);
                    float price;
                    bool tryPrice = float.TryParse(row.Cells["Price"].Value.ToString(), out price);
                    //quantity and price were converted to respective data types fine
                    if (tryQuantity && tryPrice)
                    {
                        //both are valid data types, but also need to be greater than 0
                        if (quantity > 0 && price > 0)
                        {
                            Console.Out.WriteLine(row.Cells[0].Value.ToString() + "," + quantity + "," + price + "," + row.Cells[4].Value);
                            partialQuery.Add(row.Cells[0].Value.ToString() + "," + quantity + "," + price + "," + row.Cells[4].Value);
                        }
                        else
                        {
                            row.ErrorText = "Invalid Row Data!";
                            isError = true;
                        }
                    }
                    else
                    {
                        row.ErrorText = "Invalid Row Data!";
                        isError = true;
                    }
                }
            }
            else
                //PO Input invalid pattern
                MessageBox.Show("Invalid PO ID!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            //only create PO if partialQuery list has atleast 1 value
            if (partialQuery.Count > 0 && !isError)
            {

                if (MessageBox.Show("Are you sure you want to submit this Purchase Order?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                { 
                    //create PO (doesn't require any input since PO_number is auto inc, and timestamp is CURRENT_TIME
                    Query.nonQuery("INSERT INTO purchase_order (PO_number, createdBy, status, vendor_id) VALUES('" + tbPO.Text + "','" + Employee.FirstName + " " + Employee.LastName + "','1','" + cbVendor_name.Text + "')");

                    //run through partialQuery and create insert for each result
                    foreach (string s in partialQuery)
                    {
                        //split[0] is vendorCode, split[1] is quantity, split[2] is price
                        string[] split = s.Split(',');
                        string itemID = Query.VENDORCODE_ITEMID[split[0]].ToString();
                        string itemQuantity = split[1].ToString();
                        string itemPrice = split[2].ToString();

                        //query to insert po_item combo (mainly for reporting)
                        string q = "INSERT INTO po_item VALUES('" + tbPO.Text + "','" + itemID + "','" + itemQuantity + "','" + itemPrice + "')";
                        Query.nonQuery(q);

                        //query to insert item_quantity the actual items inserted (timestamp column is CURRENT_TIMESTAMP
                        string q2 = "INSERT INTO item_quantity (item_id, quantity, price) VALUES('" + itemID + "','" + itemQuantity + "','" + itemPrice + "')";
                        Query.nonQuery(q2);
                        //Console.Out.WriteLine(q);
                        //insert new item info


                        //if checkbox is checked, update actual price using AVG concept detailed in 
                        if (split[3].ToString().Equals("True"))
                        {
                            //get all entries for specified itemID
                            DataTable res = Query.select("SELECT price, quantity, timestamp FROM ITEM_QUANTITY where item_id = '" + itemID + "' order by timestamp DESC");

                            //update actual price using average method on res DataTable
                            Query.nonQuery("UPDATE ITEM SET actual_price = '" + average(res) + "' WHERE item_id = '" + itemID + "'");
                        }
                    }

                    DataTable result = Query.select("SELECT * FROM purchase_order where status = '0' order by timestamp ASC");
                    Singleton.getMainForm().MainPODataGridView.DataSource = result;

                    //Insert the PO, requeries the results in the main tab and set focus to that tab.
                    MainForm myForm = Singleton.getMainForm();//(MainForm)POSubmitBtn.FindForm();
                    //enables the PO tab on the main form
                    myForm.ucPurchaseOrderSubmit = 1;

                    //reset form
                    resetFields();
                }


                //on submit, it will prompt the user that the information inserted will populate the DB, 
                //on yes, it inserts, no returns to the screen

                //print will create new Form with relevent print data only (only used to print, then auto dispose)
            }
            else
            {
                MessageBox.Show("Quantity and Price must be valid for each row before continuing!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void resetFields()
        {
            btnSubmit.Enabled = false;
            btnSave.Enabled = false;

            tbPO.Text = "";
            lblDate.Text = System.DateTime.Today.ToString("D");

            cbVendor_name.Text = "";

            lbItems.Items.Clear();
            btnAdd.Enabled = false;

            PODataGridView.Rows.Clear();
            btnDeleteRow.Enabled = false;

            lblName.Visible = false;
            lblAddress.Visible = false;
            lblPhone.Visible = false;
            lblDate.Visible = false;
        }

        /// <summary>
        /// do the same as button submit except it doesn't insert into item_quantity at the same time
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSave_Click(object sender, EventArgs e)
        {
            isError = false;
            foreach (DataGridViewRow row in PODataGridView.Rows)
                row.ErrorText = "";

            List<String> partialQuery = new List<String>();

            if (isValidPO)
            {
                //holds [vendorCode],[quantity],[price] to later build INSERT when obtaining PO #

                //run through each row, ignore rows with [empty, NaN, or 0 quantity/price]
                foreach (DataGridViewRow row in PODataGridView.Rows)
                {
                    //if quantity or price is null (cell content is null when empty), skip
                    if (row.Cells["Quantity"].Value == null || row.Cells["Price"].Value == null)
                    {
                        row.ErrorText = "Invalid Row Data!";
                        isError = true;
                    }

                    int quantity;
                    bool tryQuantity = int.TryParse(row.Cells["Quantity"].Value.ToString(), out quantity);
                    float price;
                    bool tryPrice = float.TryParse(row.Cells["Price"].Value.ToString(), out price);
                    //quantity and price were converted to respective data types fine
                    if (tryQuantity && tryPrice)
                    {
                        //both are valid data types, but also need to be greater than 0
                        if (quantity > 0 && price > 0)
                        {
                            Console.Out.WriteLine(row.Cells[0].Value.ToString() + "," + quantity + "," + price + "," + row.Cells[4].Value);
                            partialQuery.Add(row.Cells[0].Value.ToString() + "," + quantity + "," + price + "," + row.Cells[4].Value);
                        }
                        else
                        {
                            row.ErrorText = "Invalid Row Data!";
                            isError = true;
                        }
                    }
                    else
                    {
                        row.ErrorText = "Invalid Row Data!";
                        isError = true;
                    }
                }
            }
            else
                //PO Input invalid pattern
                MessageBox.Show("Invalid PO ID!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            //only create PO if partialQuery list has atleast 1 value
            if (partialQuery.Count > 0 && !isError)
            {
                if (MessageBox.Show("Are you sure you want to save this Purchase Order?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                {
                    //create PO (doesn't require any input since PO_number is auto inc, and timestamp is CURRENT_TIME
                    Query.nonQuery("INSERT INTO purchase_order (PO_number, createdBy, status, vendor_id) VALUES('" + tbPO.Text + "','" + Employee.FirstName + " " + Employee.LastName + "','0','" + cbVendor_name.Text + "')");

                    //run through partialQuery and create insert for each result
                    foreach (string s in partialQuery)
                    {
                        //split[0] is vendorCode, split[1] is quantity, split[2] is price
                        string[] split = s.Split(',');
                        string itemID = Query.VENDORCODE_ITEMID[split[0]].ToString();
                        string itemQuantity = split[1].ToString();
                        string itemPrice = split[2].ToString();

                        //query to insert po_item combo (mainly for reporting)
                        string q = "INSERT INTO po_item VALUES('" + tbPO.Text + "','" + itemID + "','" + itemQuantity + "','" + itemPrice + "')";
                        Query.nonQuery(q);
                    }
                    DataTable res = Query.select("SELECT * FROM purchase_order where status = '0' order by timestamp ASC");
                    Singleton.getMainForm().MainPODataGridView.DataSource = res;

                    //Insert the PO, requeries the results in the main tab and set focus to that tab.
                    MainForm myForm = Singleton.getMainForm();
                    //enables the PO tab on the main form
                    myForm.ucPurchaseOrderSubmit = 1;

                    //reset form
                    resetFields();
                }

                //on submit, it will prompt the user that the information inserted will populate the DB, 
                //on yes, it inserts, no returns to the screen

                //print will create new Form with relevent print data only (only used to print, then auto dispose)
            }
            else
            {
                MessageBox.Show("Quantity and Price must be valid for each row before continuing!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


        public void qGetVendorName()
        {
            cbVendor_name.Items.Clear();
            string query = "SELECT vendor_id FROM Vendor";
            DataTable result = Query.select(query);

            foreach (DataRow row in result.Rows)
            {
                cbVendor_name.Items.Add(row["vendor_id"].ToString());
            }
        }

        //Method that calculates the actual price for a item
        public static double average(DataTable table)
        {
            double total = 0;
            int totalQuantity = 0;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                total += (double)table.Rows[i]["price"] * (int)table.Rows[i]["quantity"];
                totalQuantity += (int)table.Rows[i]["quantity"];
            }
            return total / (double)totalQuantity;
        }

        public void populateALL_VENDORCODE()
        {
            string[] vendorcode_item_ids = new string[Query.VENDORCODE_DESC.Count];
            string[] vendorcode_item_desc = new string[Query.VENDORCODE_DESC.Count];

            Query.VENDORCODE_DESC.Keys.CopyTo(vendorcode_item_ids, 0);
            Query.VENDORCODE_DESC.Values.CopyTo(vendorcode_item_desc, 0);

            for (int i = 0; i < vendorcode_item_desc.Length; i++)
            {
                vendorcode_item_desc[i] = vendorcode_item_desc[i] + " - " + vendorcode_item_ids[i];
            }

            //only enable Add Item(s) button if the listbox has values
            if (vendorcode_item_desc.Length > 0)
                btnAdd.Enabled = true;

            //add array of values to the combo-box
            lbItems.Items.Clear();
            lbItems.Items.AddRange(vendorcode_item_desc);
        }

        private void btnNewItem_Click(object sender, EventArgs e)
        {
            new FormNewItem().Show();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            string vendor_id = this.cbVendor_name.Text.Trim();
            Query.VENDORCODE_DESC.Clear();
            Query.VENDORCODE_ITEMID.Clear();

            Query.populateHashtable("VENDORCODE_DESC", "WHERE vendor_id='" + vendor_id + "' ");
            Query.populateHashtable("VENDORCODE_ITEMID", "WHERE vendor_id='" + vendor_id + "' ");
            //remove key/value from all_itemid_desc, insert same key/value into unit_itemid_desc
            for (int i = 0; i < lbItems.SelectedItems.Count; i++)
            {
                String str = lbItems.SelectedItems[i].ToString();

                //convert selected key/value into formated string and add to unit list (it auto sorts)
                String[] split = str.Split('-');
                //extract key/value from all item
                string value = split[0].Trim();
                string key = split[1].Trim();

                //run hashtable check again to include any newly added items
                //Query.populateHashtable("VENDORCODE_ITEMID", "");

                string item_id = Query.VENDORCODE_ITEMID[key].ToString();
                DataTable prevPrice = Query.select("SELECT price FROM item_quantity WHERE item_id = '" + item_id + "' ORDER BY timestamp DESC LIMIT 0,1");
                double price = (prevPrice.Rows.Count > 0) ? (double)prevPrice.Rows[0][0] : 0.00;

                //add items to unit listbox
                PODataGridView.Rows.Add(key, value, 0, price, true);

                //remove from vendorcode
                Query.VENDORCODE_DESC.Remove(key);
            }

            //if there is more than 0 items in PODataGridView, enable remove
            if (PODataGridView.Rows.Count > 0)
            {
                btnDeleteRow.Enabled = true;
                btnSave.Enabled = true;
                btnSubmit.Enabled = true;
            }

            populateALL_VENDORCODE();
        }

        private void cbVendor_name_change(object sender, EventArgs e)
        {
            PODataGridView.Rows.Clear();
            string vendor_id = this.cbVendor_name.Text.Trim();
            Console.Out.WriteLine("Vendor name: " + vendor_id);
            Query.VENDORCODE_DESC.Clear();
            Query.VENDORCODE_ITEMID.Clear();

            Query.populateHashtable("VENDORCODE_DESC", "WHERE vendor_id='" + vendor_id + "' ");
            Query.populateHashtable("VENDORCODE_ITEMID", "WHERE vendor_id='" + vendor_id + "' ");

            //populate vendor info
            DataTable res = Query.select("SELECT name, address, location, phone FROM vendor WHERE vendor_id='" + vendor_id + "'");
            lblName.Text = res.Rows[0]["name"].ToString();
            lblAddress.Text = res.Rows[0]["address"].ToString() + "\n" + res.Rows[0]["location"].ToString();
            lblPhone.Text = res.Rows[0]["phone"].ToString();

            lblName.Visible = true;
            lblAddress.Visible = true;
            lblPhone.Visible = true;
            lblDate.Visible = true;

            populateALL_VENDORCODE();
        }

        private void deleteRows()
        {
            foreach (DataGridViewRow row in PODataGridView.SelectedRows)
            {
                string desc = row.Cells["Description"].Value.ToString() + " - " + row.Cells["vendorCode"].Value.ToString();
                //desc - vendorCode
                lbItems.Items.Add(desc);

                PODataGridView.Rows.Remove(row);
            }

            if (PODataGridView.Rows.Count < 1)
            {
                btnDeleteRow.Enabled = false;
                btnSubmit.Enabled = false;
                btnSave.Enabled = false;
            }
        }

        private void btnDeleteRow_Click(object sender, EventArgs e)
        {
            deleteRows();
        }

        //allowing interception of delete key to remove rows anywhere in the UC if at least 1 row is selected
        //then passes the key event back on to the base class
        protected override bool ProcessCmdKey(ref Message msg, Keys keyData)
        {
            if (PODataGridView.SelectedRows.Count > 0)
            {
                if (keyData == Keys.Delete)
                {
                    deleteRows();
                }
            }
            return base.ProcessCmdKey(ref msg, keyData);
        }

        private void tbPO_textChanged(object sender, EventArgs e)
        {
            isValidPO = Parser.isMatch(sender, @"^[a-zA-Z0-9]{4,9}$");

            if (isValidPO)
            {
                btnSubmit.Enabled = true;
                btnSave.Enabled = true;
            }
            else
            {
                btnSubmit.Enabled = false;
                btnSave.Enabled = false;
            }
        }

        private void linkClearPO_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            resetFields();
        }

    }
}
